﻿// DataTable Extensions from Finaquant Analytics
// Provided with the GNU General Public License (GPL)
// For license information see http://www.gnu.org/licenses/licenses.html
//
// Copyright ©  - Finaquant Analytics GmbH - http://finaquant.com/
// 
// NOTE: Non-commercial and free .net library Finaquant Protos
// which is required for these DataTable Extensions
// can be downloaded at http://finaquant.com/FinaquantProtos

using FinaquantProtos;
using System;
using System.Collections.Generic;
using System.Linq;
// using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Text;
using System.ComponentModel;
using System.Windows.Forms;
using System.Threading;
using System.IO;

// enforce CLS compliance for all public types in this assembly
[assembly: CLSCompliant(true)]

namespace FinaquantDataTableExtensions
{
    /// <summary>
    /// Extension methods for DataTable class of .net framework 
    /// </summary>
    public static class FQ_DataTable_Extensions
    {
        /// <summary>
        /// Get data type of column
        /// </summary>
        public static Type GetColumnDataType(this DataTable tbl, string ColumnName)
        {
            try
            {
                return tbl.Columns[ColumnName].DataType;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_GetColumnDataType: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Get data type of column
        /// </summary>
        public static Type GetColumnDataType(this DataTable tbl, int ColumnIndex)
        {
            try
            {
                return tbl.Columns[ColumnIndex].DataType;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_GetColumnDataType: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Get table element with given column and row indices
        /// </summary>
        public static object GetElementValue(this DataTable tbl, int ColInd, int RowInd)
        {
            try
            {
                return tbl.Rows[RowInd][ColInd];
            }
            catch (Exception ex)
            {
                throw new Exception("OS_GetElementValue: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Get table element with given column names and row indices
        /// </summary>
        public static object GetElementValue(this DataTable tbl, string ColumnName, int RowInd)
        {
            try
            {
                return tbl.Rows[RowInd][ColumnName];
            }
            catch (Exception ex)
            {
                throw new Exception("OS_GetElementValue: \n" + ex.Message);
            }
        }

        /// <summary>Assign value to a table element with given column and row indices
        /// </summary>
        public static void SetElementValue(this DataTable tbl, string ColumnName, int RowInd,
            object ElementValue)
        {
            try
            {
                tbl.Rows[RowInd][ColumnName] = ElementValue;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_SetElementValue: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Convert DataTable into a printable string
        /// </summary>
        /// <returns>Formatted string</returns>
        public static string ConvertToString(this DataTable tbl)
        {
            try
            {
                // check if table is empty
                if (tbl == null || tbl.Columns.Count == 0)
                    return "OS_ConvertToString: EMPTY DataTable";

                StringBuilder builder = new StringBuilder();

                //  columns
                for (int i = 0; i < tbl.Columns.Count; i++)
                {
                    builder.Append(tbl.Columns[i].ColumnName + "\t");
                }

                // dashes
                builder.AppendLine();
                int StrLen = builder.ToString().Length + tbl.Columns.Count;
                for (int i = 0; i < StrLen; i++) builder.Append("-");
                builder.AppendLine();

                // rows
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    for (int j = 0; j < tbl.Columns.Count; j++)
                    {
                        builder.Append(tbl.Rows[i][j].ToString() + "\t");
                    }
                    builder.AppendLine();
                }
                return builder.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ConvertToString: " + ex.Message);
            }
        }

        /// <summary>
        /// Convert DataTable into  a MatrixTable object
        /// </summary>
        /// <param name="tbl">Input table of type DataTable</param>
        /// <param name="md">Master data</param>
        /// <param name="TextReplaceNull">Replacement text value for null in input table</param>
        /// <param name="NumReplaceNull">Replacement integer value for null in input table</param>
        /// <param name="KeyFigReplaceNull">Replacement floating value for null in input table</param>
        /// <returns>MatrixTable object</returns>
        public static MatrixTable ConvertToMatrixTable(this DataTable tbl, MetaData md,
            string TextReplaceNull = "NULL", int NumReplaceNull = 0, double KeyFigReplaceNull = 0)
        {
            // check if table is empty
            if (tbl == null || tbl.Columns.Count == 0)
                throw new Exception("OS_ConvertToMatrixTable: Null or empty input table!");

            // init variables
            var tfields = TableFields.CreateEmptyTableFields(md);
            FieldType ftype;

            // add columns
            for (int i = 0; i < tbl.Columns.Count; i++)
            {
                // check master data
                ftype = MetaData.GetFieldType(md, tbl.Columns[i].ColumnName);

                switch (ftype)
                {
                    case FieldType.TextAttribute:
                    case FieldType.IntegerAttribute:
                    case FieldType.DateAttribute:
                    case FieldType.KeyFigure:

                        TableFields.AddNewField(tfields, tbl.Columns[i].ColumnName);
                        break;

                    case FieldType.Undefined:
                        throw new Exception("OS_ConvertToMatrixTable: DataTable field " + tbl.Columns[i].ColumnName +
                            " is not defined in master data!\n");
                }
            }   // for i

            // init variables
            var tblout = MatrixTable.CreateEmptyTable(tfields);     // table without rows
            string sval = "EMPTY";
            int ival = 0;
            double dval = 0.0;
            TableRow trow;

            try
            {
                // add rows
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    trow = TableRow.CreateDefaultTableRow(tfields);

                    for (int j = 0; j < tbl.Columns.Count; j++)
                    {
                        // check master data
                        ftype = MetaData.GetFieldType(md, tbl.Columns[j].ColumnName);

                        switch (ftype)
                        {
                            case FieldType.TextAttribute:
                                if (tbl.Rows[i][j] == null)
                                    sval = TextReplaceNull;
                                else
                                    sval = tbl.Rows[i][j].ToString();
                                TableRow.SetTextAttributeValue(trow, tbl.Columns[j].ColumnName, sval);
                                break;

                            case FieldType.IntegerAttribute:
                                if (tbl.Rows[i][j] == null)
                                    ival = NumReplaceNull;
                                else
                                    ival = Convert.ToInt32(tbl.Rows[i][j].ToString());
                                TableRow.SetNumAttributeValue(trow, tbl.Columns[j].ColumnName, ival);
                                break;

                            case FieldType.DateAttribute:
                                if (tbl.Rows[i][j] == null)
                                    ival = NumReplaceNull;
                                else
                                {
                                    if (tbl.Columns[j].DataType == typeof(DateTime))
                                    {
                                        ival = (int)DateFunctions.DateToNumber((DateTime)tbl.Rows[i][j]);
                                    }
                                    else
                                    {
                                        ival = (int)tbl.Rows[i][j];
                                    }
                                }
                                TableRow.SetNumAttributeValue(trow, tbl.Columns[j].ColumnName, ival);
                                break;

                            case FieldType.KeyFigure:
                                if (tbl.Rows[i][j] == null)
                                    dval = KeyFigReplaceNull;
                                else
                                    dval = Convert.ToDouble(tbl.Rows[i][j].ToString());
                                TableRow.SetKeyFigureValue(trow, tbl.Columns[j].ColumnName, dval);
                                break;
                        }
                    }   // for j
                    tblout = MatrixTable.AddRowToTable(tblout, trow);
                }
                return tblout;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ConvertToMatrixTable: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Convert TableMatrix to DataTable.
        /// - numeric attributes of type date are converted to DateTime
        /// </summary>
        public static DataTable ImportFromMatrixTable(this DataTable tbl, MatrixTable mtbl)
        {
            try
            {
                var dtable = new DataTable();

                // check if empty table
                if (mtbl == null || mtbl.IsEmpty) return dtable;

                FieldType ftype;
                var ColNames = mtbl.ColumnNames;

                // add columns
                for (int i = 0; i < ColNames.nLength; i++)
                {
                    ftype = MetaData.GetFieldType(mtbl.metaData, ColNames[i]);

                    switch (ftype)
                    {
                        case FieldType.TextAttribute:
                            dtable.Columns.Add(ColNames[i], typeof(string));
                            break;
                        case FieldType.IntegerAttribute:
                            dtable.Columns.Add(ColNames[i], typeof(int));
                            break;
                        case FieldType.DateAttribute:
                            dtable.Columns.Add(ColNames[i], typeof(DateTime));
                            break;
                        case FieldType.KeyFigure:
                            dtable.Columns.Add(ColNames[i], typeof(double));
                            break;
                    }
                } // for i

                // add rows
                TableRow trow;

                for (int i = 0; i < mtbl.RowCount; i++)
                {
                    trow = mtbl.GetTableRow(i);
                    dtable.Rows.Add();        // add new empty row to table

                    for (int j = 0; j < ColNames.nLength; j++)
                    {
                        ftype = MetaData.GetFieldType(mtbl.metaData, ColNames[j]);

                        switch (ftype)
                        {
                            case FieldType.TextAttribute:
                                dtable.Rows[i][ColNames[j]] = TableRow.GetTextAttributeValue(trow, ColNames[j]);
                                break;
                            case FieldType.IntegerAttribute:
                                dtable.Rows[i][ColNames[j]] = TableRow.GetNumAttributeValue(trow, ColNames[j]);
                                break;
                            case FieldType.DateAttribute:
                                dtable.Rows[i][ColNames[j]] = DateFunctions.NumberToDate(TableRow.GetNumAttributeValue(trow, ColNames[j]));
                                break;
                            case FieldType.KeyFigure:
                                dtable.Rows[i][ColNames[j]] = TableRow.GetKeyFigureValue(trow, ColNames[j]);
                                break;
                        }
                    }
                }
                return dtable;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ImportFromMatrixTable: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Column partition: Return a DataTable with selected columns
        /// </summary>
        /// <param name="tbl">DataTable</param>
        /// <param name="ColumnNames">Column names</param>
        /// <returns>Subtable with selected columns</returns>
        /// <remarks>
        /// - error if input vector with column names is null or empty
        /// - error if any column name in vector is not contained in DataTable
        /// </remarks>
        public static DataTable PartitionColumn(this DataTable tbl, TextVector ColumnNames)
        {
            try
            {
                // check if empty table
                if (tbl == null || tbl.Columns.Count == 0)
                    throw new Exception("OS_PartitionColumn: Null or empty input table!\n");

                // check if empty input vector
                if (ColumnNames == null || ColumnNames.IsEmpty)
                    throw new Exception("OS_PartitionColumn: Null or empty input vector ColumnNames!\n");

                // ensure that input vector is unique
                ColumnNames = TextVector.Unique(ColumnNames);

                var tblout = new DataTable();

                // add required columns to tblout
                for (int i = 0; i < ColumnNames.nLength; i++)
                {
                    if (!tbl.Columns.Contains(ColumnNames[i]))
                    {
                        throw new Exception("OS_PartitionColumn: Field(s) not found in input table!\n");
                    }
                    else
                    {
                        tblout.Columns.Add(ColumnNames[i], tbl.Columns[ColumnNames[i]].DataType);
                    }
                }

                // add rows to tblout
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    tblout.Rows.Add();
                    for (int j = 0; j < ColumnNames.nLength; j++)
                    {
                        tblout.Rows[i][ColumnNames[j]] = tbl.Rows[i][ColumnNames[j]];
                    }
                }

                return tblout;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_PartitionColumn: " + ex.Message);
            }
        }

        /// <summary>
        /// Return subtable with selected column indices
        /// </summary>
        /// <param name="tbl">DataTable</param>
        /// <param name="ColumnIndices">Column indices</param>
        /// <returns>Subtable with selected columns</returns>
        /// <remarks>
        /// - error if input vector with column indices is null or empty
        /// - error if any column index in vector is not proper
        /// </remarks>
        public static DataTable PartitionColumn(this DataTable tbl, NumVector ColumnIndices)
        {
            // check if empty table
            if (tbl == null || tbl.Columns.Count == 0)
                throw new Exception("OS_PartitionColumn: Null or empty input table!\n");

            // check if empty index vector
            if (ColumnIndices == null || ColumnIndices.IsEmpty)
                throw new Exception("OS_PartitionColumn: Null or empty input vector ColumnIndices!\n");

            // ensure that index vector is unique
            ColumnIndices = NumVector.Unique(ColumnIndices);

            var tblout = new DataTable();

            // add required columns to tblout
            for (int i = 0; i < ColumnIndices.nLength; i++)
            {
                if (ColumnIndices[i] < 0 || ColumnIndices[i] >= tbl.Columns.Count)
                {
                    throw new Exception("OS_PartitionColumn: Improper index value(s)!");
                }
                else
                {
                    tblout.Columns.Add(tbl.Columns[ColumnIndices[i]].ColumnName,
                        tbl.Columns[ColumnIndices[i]].DataType);
                }
            }

            // add rows to tblout
            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                tblout.Rows.Add();
                for (int j = 0; j < ColumnIndices.nLength; j++)
                {
                    tblout.Rows[i][j] = tblout.Rows[i][ColumnIndices[j]];
                }
            }
            return tblout;
        }

        /// <summary>Return subtable with selected row indices
        /// </summary>
        /// <param name="tbl">DataTable</param>
        /// <param name="RowIndices">Row indices</param>
        /// <returns>Subtable with selected rows</returns>
        /// <remarks>
        /// - error if input table is null or empty
        /// - error if index vector is null or empty
        /// - error if improper index value in vector
        /// </remarks>
        public static DataTable PartitionRow(this DataTable tbl, NumVector RowIndices)
        {
            // check if empty table
            if (tbl == null || tbl.Columns.Count == 0)
                throw new Exception("OS_PartitionRow: Null or empty input table!\n");

            // check if empty index vector
            if (RowIndices == null || RowIndices.IsEmpty)
                throw new Exception("OS_PartitionRow: Null or empty input vector RowIndices!\n");

            // check if improper index
            if (RowIndices.toArray.Min() < 0 || RowIndices.toArray.Max() >= tbl.Rows.Count)
                throw new Exception("OS_PartitionRow: Improper index value(s) in vector RowIndices!\n");

            // initiate table to be returned
            var tblout = new DataTable();

            // add columns to tblout
            for (int i = 0; i < tbl.Columns.Count; i++)
            {

                tblout.Columns.Add(tbl.Columns[i].ColumnName, tbl.Columns[i].DataType);
            }

            // add rows to tblout
            for (int i = 0; i < RowIndices.nLength; i++)
            {
                tblout.Rows.Add();
                for (int j = 0; j < tbl.Columns.Count; j++)
                {
                    tblout.Rows[i][j] = tbl.Rows[RowIndices[i]][j];
                }
            }
            return tblout;
        }

        /// <summary>Return subtable with selected columns and rows
        /// </summary>
        public static DataTable Partition(this DataTable tbl, TextVector ColumnNames, NumVector RowIndices)
        {
            var tblout = tbl.PartitionColumn(ColumnNames);
            return tblout.PartitionRow(RowIndices);
        }

        /// <summary>
        /// View DataTable in grid view (read-only), multi-thread
        /// </summary>
        /// <param name="Tbl">DataTable input</param>
        /// <param name="Header">Header text for table viewer</param>
        /// <returns>TableViewer object</returns>
        public static TableViewer ViewTable_MultiThread(this DataTable Tbl, string Header = "Table Viewer")
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("OS_ViewTable_MultiThread: Null or empty input table!\n");

            var tw = new TableViewer(Tbl, Header);

            Thread tr = new Thread(() =>
            {
                try
                {
                    Application.EnableVisualStyles();
                    Application.SetCompatibleTextRenderingDefault(false);

                    Application.Run(tw);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("OS_ViewTable_MultiThread: " + ex.Message);
                }
            });
            tr.Start();
            return tw;
        }

        /// <summary>
        /// View DataTable in grid view (read-only), single-thread
        /// </summary>
        /// <param name="Tbl">DataTable input</param>
        /// <param name="Header">Header text for table viewer</param>
        /// <returns>TableViewer object</returns>
        public static TableViewer ViewTable_SingleThread(this DataTable Tbl, string Header = "Table Viewer")
        {
            try
            {
                if (Tbl == null || Tbl.Columns.Count == 0)
                    throw new Exception("ViewTable_SingleThread: Null or empty input table!\n");

                // Application.EnableVisualStyles();
                // Application.SetCompatibleTextRenderingDefault(false);

                TableViewer tw = new TableViewer(Tbl, Header);
                tw.Show();

                return tw;
            }
            catch (Exception ex)
            {
                throw new Exception("FQ_DataTable_Extensions.FQ_ViewTable2: " + ex.Message);
            }
        }

        /// <summary>
        /// Save DataTable as an XML file.
        /// - Add a timestamp like "_14_09_2012_14_21_04" to file name if IfAddTimeStamp = true
        /// </summary>
        /// <param name="Tbl">Data table</param>
        /// <param name="Filename">A file name like "InventoryTable" without file extension</param>
        /// <param name="FileDir">A valid directory like @"C:\Users\John\Documents\</param>
        /// <param name="IfAddTimeStamp">If true, add timestamp to file name </param>
        public static void ExportTableToXMLfile(this DataTable Tbl, string Filename, string FileDir = "",
            bool IfAddTimeStamp = true)
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("OS_StoreTableAsXMLfile: Null or empty input table!\n");

            Tbl.TableName = Filename;       // a table name is required for XML storage
            var filepath = FileDir + Filename;

            if (IfAddTimeStamp)
            {
                string timestr = DateTime.Now.ToLocalTime().ToString();     // a string like 14.09.2012 13:56:03
                timestr = Regex.Replace(timestr, @"[:\s\.]", "_");
                filepath = filepath + "_" + timestr;
            }

            try
            {
                Tbl.WriteXmlSchema(filepath + ".xsd");
                Tbl.WriteXml(filepath + ".xml");
            }
            catch (Exception ex)
            {
                throw new Exception("OS_StoreTableAsXMLfile: Data table could not be written into an XML file.\n"
                    + ex.Message);
            }
        }

        /// <summary>
        /// Read DataTable from an XML file
        /// </summary>
        /// <param name="Tbl">Data table</param>
        /// <param name="Filename">A file name like "InventoryTable" without file extension</param>
        /// <param name="FileDir">A valid directory like @"C:\Users\John\Documents\</param>
        /// <returns>DataTable object</returns>
        public static DataTable ImportTableFromXMLfile(this DataTable Tbl, string Filename, string FileDir = "")
        {
            // read data table from XML file
            var TblOut = new DataTable();
            var filepath = FileDir + Filename;

            try
            {
                TblOut.ReadXmlSchema(filepath + ".xsd");
                TblOut.ReadXml(filepath + ".xml");
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ReadTableFromXMLfile: Data table could not be read from XML file.\n"
                    + ex.Message);
            }
            return TblOut;
        }

        /// <summary>
        /// Sort table rows after given fields
        /// </summary>
        /// <param name="Tbl">Input table</param>
        /// <param name="SortStr">Comma separated field names and sort option, like "field1 ASC, field2 DESC"</param>
        /// <returns>Sorted DataTable</returns>
        /// <remarks>
        /// - default sort option is ASC; need not be stated explicitly
        /// </remarks>
        public static DataTable Sort(this DataTable Tbl, string SortStr)
        {
            try
            {
                DataView dtview = new DataView(Tbl);
                dtview.Sort = SortStr;
                return dtview.ToTable();
            }
            catch (Exception ex)
            {
                throw new Exception("OS_Sort: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Export DataTable to CSV file with semicolon as delimiter
        /// </summary>
        /// <param name="Tbl">DataTable object</param>
        /// <param name="filepath">Complete file path including file name</param>
        public static void ExportToCSVfile(this DataTable Tbl, string filepath)
        {
            try
            {
                // Create CSV file
                StreamWriter sw = new StreamWriter(filepath, false);

                // write headers
                int ColumnCount = Tbl.Columns.Count;

                for (int i = 0; i < ColumnCount; i++)
                {
                    sw.Write(Tbl.Columns[i]);
                    if (i < ColumnCount - 1)
                    {
                        sw.Write(";");
                    }
                }
                sw.Write(sw.NewLine);

                // write rows
                foreach (DataRow dr in Tbl.Rows)
                {
                    for (int i = 0; i < ColumnCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            sw.Write(dr[i].ToString());
                        }
                        if (i < ColumnCount - 1)
                        {
                            sw.Write(";");
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("FQ_DataTable_Extensions.FQ_ExportToCSVfile: \n" + ex.Message);
            }
        }

        /// Export DataTable to CSV file with semicolon as delimiter
        /// and open with an associated windows application.
        /// </summary>
        /// <param name="Tbl">DataTable object</param>
        /// <param name="filepath">Complete file path including file name</param>
        /// <remarks>
        /// Stores a file named "MyCSVfile.csv" in the temp folder
        /// </remarks>
        public static void ExportToCSVfile(this DataTable Tbl)
        {
            try
            {
                // construct filepath in TEMP folder of current user
                string tempPath = System.IO.Path.GetTempPath();
                string filepath = tempPath + "\\" + "FinaquantCSVfile_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";

                // export to file
                Tbl.ExportToCSVfile(filepath);

                // open file with a windows application
                System.Diagnostics.Process.Start(filepath);
            }
            catch (Exception ex)
            {
                throw new Exception("FQ_DataTable_Extensions.FQ_ExportToCSVfile: \n" + ex.Message);
            }
        }

        /// <summary>
        /// Import DataTable from CSV file (semicolon as delimiter)
        /// </summary>
        /// <param name="Tbl">DataTable</param>
        /// <param name="md">MetaData</param>
        /// <param name="filepath">Full file path; directory and file name</param>
        /// <returns>DataTable object</returns>
        public static DataTable ImportFromCSVfile(this DataTable Tbl, MetaData md, string filepath)
        {
            StreamReader reader;
            try
            {
                reader = new StreamReader(File.OpenRead(filepath));
            }
            catch (Exception ex)
            {
                throw new Exception("FQ_DataTable_Extensions.FQ_ImportFromCSVfile: \n"
                    + "Could not open file! Please check directory and file name.\n" + ex.Message);
            }

            try
            {
                // create DataTable
                var dtable = new DataTable();
                int LineCount = 0;

                // variables
                string fieldname;
                var FieldTypes = new List<FieldType>();
                string line;
                string[] values;
                DataRow drow;

                while (!reader.EndOfStream)
                {
                    LineCount += 1;
                    line = reader.ReadLine();
                    values = line.Split(';');

                    // read header line and add columns to table
                    if (LineCount == 1)
                    {
                        for (int i = 0; i < values.Length; i++)
                        {
                            // convert to lower letters
                            fieldname = values[i].ToLower();

                            // get field type from MetaData
                            FieldTypes.Add(MetaData.GetFieldType(md, fieldname));

                            // check if field is defined in MetaData
                            if (FieldTypes[i] == FieldType.Undefined)
                            {
                                throw new Exception("FQ_DataTable_Extensions.FQ_ImportFromCSVfile: "
                                    + "Field " + fieldname + " is not defined in MetaData!\n");
                            }

                            // checks OK, add column
                            switch (FieldTypes[i])
                            {
                                case FieldType.TextAttribute:
                                    dtable.Columns.Add(fieldname, typeof(string));
                                    break;
                                case FieldType.IntegerAttribute:
                                    dtable.Columns.Add(fieldname, typeof(int));
                                    break;
                                case FieldType.DateAttribute:
                                    dtable.Columns.Add(fieldname, typeof(DateTime));
                                    break;
                                case FieldType.KeyFigure:
                                    dtable.Columns.Add(fieldname, typeof(double));
                                    break;
                            }
                        }
                    }

                    // add rows to DataTable
                    if (LineCount > 1)
                    {
                        // check number of values in the line
                        if (values.Length != dtable.Columns.Count)
                        {
                            throw new Exception("FQ_DataTable_Extensions.FQ_ImportFromCSVfile: "
                                + "Number of values in file line does not match column count!\n");
                        }

                        // get row data
                        drow = dtable.NewRow();

                        for (int j = 0; j < values.Length; j++)
                        {

                            switch (FieldTypes[j])
                            {
                                case FieldType.TextAttribute:
                                    drow[j] = values[j];
                                    break;
                                case FieldType.IntegerAttribute:
                                    drow[j] = Convert.ToInt32(values[j]);
                                    break;
                                case FieldType.DateAttribute:
                                    drow[j] = Convert.ToDateTime(values[j]);
                                    break;
                                case FieldType.KeyFigure:
                                    drow[j] = Convert.ToDouble(values[j]);
                                    break;
                            }
                        }
                        dtable.Rows.Add(drow);
                    }   // if
                }   // while

                // return DataTable
                return dtable;
            }
            catch (Exception ex)
            {
                throw new Exception("FQ_DataTable_Extensions.FQ_ImportFromCSVfile: \n" + ex.Message);
            }

        }

        // import/export DataTable from/to Excel
        // These excluded methods can work only if you have the required MS excel libraries on your computer
        // How to enable:
        // 1) Add following references to project OpenSource in Visual Studio: 
        // - Microsoft Office XX Object Library
        // - Microsoft Excel XX Object Library
        // 2) uncomment "using Excel = Microsoft.Office.Interop.Excel;" at the top
#if UNDEFINED
        /// <summary>
        /// Export DataTable into an excel file.
        /// Works only if MS Excel is already installed on computer.
        /// </summary>
        /// <param name="Tbl">Data table</param>
        /// <param name="ExcelFilePath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
        /// <remarks>
        /// - Save excel file without ever making it visible if filepath is given
        /// - don't save excel file, just make it visible if no filepath is given
        /// - see book C# 2010 & .NET 4, p720
        /// </remarks>
        public static void ExportToExcelFile(this DataTable Tbl, string ExcelFilePath = null)
        {
            try
            {
                if (Tbl == null || Tbl.Columns.Count == 0)
                    throw new Exception("OS_ExportToExcelFile: Null or empty input table!\n");

                // load excel, and create a new workbook
                Excel.Application excelApp = new Excel.Application();
                excelApp.Workbooks.Add();

                // single worksheet
                Excel._Worksheet workSheet = excelApp.ActiveSheet;

                // column headings
                for (int i = 0; i < Tbl.Columns.Count; i++)
                {
                    workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
                }

                // rows
                for (int i = 0; i < Tbl.Rows.Count; i++)
                {
                    // to do: format datetime values before printing
                    for (int j = 0; j < Tbl.Columns.Count; j++)
                    {
                        workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                    }
                }

                // format worksheet (AutoFormat?)
                // workSheet.UsedRange


                // check fielpath
                if (ExcelFilePath != null && ExcelFilePath != "")
                {
                    try
                    {
                        workSheet.SaveAs(ExcelFilePath);
                        excelApp.Quit();
                        MessageBox.Show("Excel file saved!");
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("OS_ExportToExcelFile: Excel file could not be saved.\n"
                            + ex.Message);
                    }
                }
                else    // no filepath is given
                {
                    excelApp.Visible = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ExportToExcelFile: " + ex.Message);
            }
        }

        /// <summary>
        /// Read DataTable from a sheet of excel file.
        /// Header line in excel sheet must contain field names.
        /// </summary>
        /// <param name="Tbl">DataTable input</param>
        /// <param name="Filepath">A valid file path like  @"C:\Users\John\Documents\Inventory.xlsx</param>
        /// <param name="SheetName">Name of excel sheet</param>
        public static DataTable ImportFromExcelFile(this DataTable Tbl, string Filepath, string SheetName)
        {
            try
            {
                var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", Filepath);
                var adapter = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$]", connectionString);

                var TblOut = new DataTable();
                adapter.FillSchema(TblOut, SchemaType.Source);
                // column types can be adjusted here before fill
                adapter.Fill(TblOut);
                return TblOut;
            }
            catch (Exception ex)
            {
                throw new Exception("OS_ImportFromExcelFile: Could not read table from excel file!\n" + ex.Message + "\n");
            }
        }
#endif

    }
}
